package com.samsungmobile.common.db;

import java.io.CharArrayReader;
import java.io.Reader;
import java.io.Writer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Vector;

import oracle.jdbc.OracleResultSet;
import oracle.sql.CLOB;

import com.samsungmobile.common.Logger;

public class DBAutoManager {
	
	/**
	 * �����ʵ��� ���ϵ������� query�� ���� �� ����
	 * 
	 * @param sQuery
	 * @return
	 */
	public static String singleValueQuery(String sQuery) {
		
		Connection conn=null;	  	
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		String reValue = "";
 
		conn=DBManager.getConnection();

		try {
			Logger.LOG_NOHUP.info("sQuery ------> \n" + sQuery);
	
			pstmt=conn.prepareStatement(sQuery);	
			rs=pstmt.executeQuery();
			reValue = DBManager.singleValueResult(rs);
			Logger.LOG_NOHUP.info(reValue + " records selected !!!!");
	
		} catch (SQLException se) {
			se.printStackTrace();
			Logger.LOG_NOHUP.debug(se.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
			Logger.LOG_NOHUP.debug(e.toString()); 				
		} finally {
			DBManager.release(rs,pstmt,conn);
		}

		return reValue;
	}
	
	/**
	 * �����ʵ��� ���ϵ������� query�� ���� �� ����
	 * 
	 * @param sQuery
	 * @return
	 */
	public static String singleValueQuery(String sQuery, Vector vParam) {
		
		Connection conn=null;	  	
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		String reValue = "";
 
		conn=DBManager.getConnection();

		try {
			Logger.LOG_NOHUP.info("sQuery ------> \n" + sQuery);
			Logger.LOG_NOHUP.info("PARAM ------> \n" + printParameter(vParam));
			
			pstmt=conn.prepareStatement(sQuery);
			DBManager.setValues(pstmt, vParam);	
			rs=pstmt.executeQuery();
			reValue = DBManager.singleValueResult(rs);
			Logger.LOG_NOHUP.info(reValue + " records selected !!!!");
	
		} catch (SQLException se) {
			se.printStackTrace();
			Logger.LOG_NOHUP.debug(se.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
			Logger.LOG_NOHUP.debug(e.toString()); 				
		} finally {
			DBManager.release(rs,pstmt,conn);
		}

		return reValue;
	}
	
	/**
	 * Select ��� Vector�� ��Ƽ� �����Ѵ�.
	 * 
	 * @param query
	 */
	public static Vector selectQuery(String sQuery){
	
		Connection conn=null;	  	
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		Vector v = null;
		 
		conn=DBManager.getConnection();

		try {
			Logger.LOG_NOHUP.info("sQuery ------> \n" + sQuery);
			
			pstmt=conn.prepareStatement(sQuery);	
			rs=pstmt.executeQuery();
			v = DBManager.multiRowResult(rs);
			Logger.LOG_NOHUP.info(v.size() + " records selected !!!!");
			
		} catch (SQLException se) {
			se.printStackTrace();
			Logger.LOG_NOHUP.debug(se.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
			Logger.LOG_NOHUP.debug(e.toString()); 				
		} finally {
			DBManager.release(rs,pstmt,conn);
		}
		
		return v;
	}

	/**
	 * Select ��� Vector�� ��Ƽ� �����Ѵ�.
	 * 
	 * @param sQuery
	 * @param vParam
	 */
	public static Vector selectQuery(String sQuery, Vector vParam){

		Connection conn=null;	  	
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		Vector v = null;
	 
		conn=DBManager.getConnection();

		try {
			Logger.LOG_NOHUP.info("QUERY ------> \n" + sQuery);
			Logger.LOG_NOHUP.info("PARAM ------> \n" + printParameter(vParam));
		
			pstmt=conn.prepareStatement(sQuery);	
			DBManager.setValues(pstmt, vParam);
			rs=pstmt.executeQuery();
			v = DBManager.multiRowResult(rs);
			Logger.LOG_NOHUP.info(v.size() + " records selected !!!!");
		
		} catch (SQLException se) {
			se.printStackTrace();
			Logger.LOG_NOHUP.debug(se.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
			Logger.LOG_NOHUP.debug(e.toString()); 				
		} finally {
			DBManager.release(rs,pstmt,conn);
		}
	
		return v;
	}

	/**
	 * insert/update/delete ��� true/false�� �����Ѵ�.
	 * 
	 * @param sQuery
	 */
	public static boolean updateQuery(String sQuery, Vector vParam){

		Connection conn=null;	  	
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		Vector v = null;
		boolean bValue = false;
	 
		conn=DBManager.getConnection();

		try {
			Logger.LOG_NOHUP.info("QUERY ------> \n" + sQuery);
			Logger.LOG_NOHUP.info("PARAM ------> \n" + printParameter(vParam));
			
			pstmt=conn.prepareStatement(sQuery);	
			DBManager.setValues(pstmt, vParam);
			pstmt.executeUpdate();
		
			bValue = true;
			Logger.LOG_NOHUP.info("GUBUN TAG : UPDATE  excute!!" );
			
		} catch (SQLException se) {
			se.printStackTrace();
			Logger.LOG_NOHUP.debug(se.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
			Logger.LOG_NOHUP.debug(e.toString()); 				
		} finally {
			DBManager.release(rs,pstmt,conn);
		}

		return bValue;
	}


	/**
	 * insert/update/delete ��� true/false�� �����Ѵ�.
	 * 
	 * @param sQuery
	 */
	public static boolean updateQuery(String sQuery){

		Connection conn=null;	  	
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		Vector v = null;
		boolean bValue = false;
	 
		conn=DBManager.getConnection();

		try {
			Logger.LOG_NOHUP.info("QUERY ------> \n" + sQuery);
		
			pstmt=conn.prepareStatement(sQuery);	
			pstmt.executeUpdate();
		
			bValue = true;
			Logger.LOG_NOHUP.info("GUBUN TAG : UPDATE  excute!!" );
			
		} catch (SQLException se) {
			se.printStackTrace();
			Logger.LOG_NOHUP.debug(se.getMessage());
		} catch (Exception e) {
			e.printStackTrace();
			Logger.LOG_NOHUP.debug(e.toString()); 				
		} finally {
			DBManager.release(rs,pstmt,conn);
		}

		return bValue;
	}
	
	/**
	 * insert/update/delete ��� true/false�� �����Ѵ�.
	 * 
	 * @param sQuery
	 */
	public static boolean updateQuery(String[] sQuerys) throws SQLException{

		Connection conn=null;	  	
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		Vector v = null;
		boolean bValue = false;
 
		conn=DBManager.getConnection();

		try {

			conn.setAutoCommit(false);
			
			for(int i=0; i<sQuerys.length; i++){
				String sQuery = sQuerys[i];
				Logger.LOG_NOHUP.info("QUERY ------> \n" + sQuery);
				pstmt=conn.prepareStatement(sQuery);	
				pstmt.executeUpdate();
			}
			conn.commit();
			
			bValue = true;
			Logger.LOG_NOHUP.info("GUBUN TAG : UPDATE  excute!!" );
		
		} catch (SQLException se) {
			conn.rollback();
			se.printStackTrace();
			Logger.LOG_NOHUP.debug(se.getMessage());
		} catch (Exception e) {
			conn.rollback();
			e.printStackTrace();
			Logger.LOG_NOHUP.debug(e.toString()); 				
		} finally {
			conn.setAutoCommit(true);
			DBManager.release(rs,pstmt,conn);
		}

		return bValue;
	}
	
	/**
	 * insert/update/delete ��� true/false�� �����Ѵ�.
	 * 
	 * @param sQuery
	 */
	public static boolean updateQuery(String[] sQuerys, Vector[] vParam) throws SQLException{

		Connection conn=null;	  	
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		Vector v = null;
		boolean bValue = false;

		conn=DBManager.getConnection();

		try {

			conn.setAutoCommit(false);
		
			for(int i=0; i<sQuerys.length; i++){
				String sQuery = sQuerys[i];
				Vector param = vParam[i];
				Logger.LOG_NOHUP.info("QUERY ------> \n" + sQuery);
				Logger.LOG_NOHUP.info("PARAM ------> \n" + printParameter(param));
				
				pstmt=conn.prepareStatement(sQuery);
				DBManager.setValues(pstmt, param);
				pstmt.executeUpdate();
			}
			conn.commit();
		
			bValue = true;
			Logger.LOG_NOHUP.info("GUBUN TAG : UPDATE  excute!!" );
	
		} catch (SQLException se) {
			conn.rollback();
			se.printStackTrace();
			Logger.LOG_NOHUP.debug(se.getMessage());
		} catch (Exception e) {
			conn.rollback();
			e.printStackTrace();
			Logger.LOG_NOHUP.debug(e.toString()); 				
		} finally {
			conn.setAutoCommit(true);
			DBManager.release(rs,pstmt,conn);
		}

		return bValue;
	}
	
	/**
	 * insert/update/delete ��� true/false�� �����Ѵ�.
	 * 
	 * @param sQuery
	 */
	public static boolean updateQuery(StringBuffer[] sbQuerys, Vector[] vParam) throws SQLException{

		Connection conn=null;	  	
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		Vector v = null;
		boolean bValue = false;

		conn=DBManager.getConnection();

		try {

			conn.setAutoCommit(false);
		
			for(int i=0; i<sbQuerys.length; i++){
				String sQuery = sbQuerys[i].toString();
				Vector param = vParam[i];
				Logger.LOG_NOHUP.info("QUERY ------> \n" + sQuery);
				Logger.LOG_NOHUP.info("PARAM ------> \n" + printParameter(param));
				
				pstmt=conn.prepareStatement(sQuery);
				DBManager.setValues(pstmt, param);
				pstmt.executeUpdate();
			}
			conn.commit();
		
			bValue = true;
			Logger.LOG_NOHUP.info("GUBUN TAG : UPDATE  excute!!" );
	
		} catch (SQLException se) {
			conn.rollback();
			se.printStackTrace();
			Logger.LOG_NOHUP.debug(se.getMessage());
		} catch (Exception e) {
			conn.rollback();
			e.printStackTrace();
			Logger.LOG_NOHUP.debug(e.toString()); 				
		} finally {
			conn.setAutoCommit(true);
			DBManager.release(rs,pstmt,conn);
		}

		return bValue;
	}	
	
	/**
	 * insert/update/delete ��� true/false�� �����Ѵ�.
	 * 
	 * @param sQuery
	 */
	public static boolean updateQuery(ArrayList parameters) throws SQLException{

		Connection conn=null;	  	
		//PreparedStatement pstmt=null;
		ResultSet rs=null;
		Vector v = null;
		boolean bValue = false;
 
		conn=DBManager.getConnection();

		try {
			

			conn.setAutoCommit(false);
			
			for(int i=0; i<parameters.size(); i++){
				
				PreparedStatement pstmt=null;

				HashMap hm = (HashMap) parameters.get(i);
				String query = (String) hm.get("QUERY");
				Vector param = (Vector) hm.get("PARAM");
				
				Logger.LOG_NOHUP.info("QUERY ------> \n" + query);				
				Logger.LOG_NOHUP.info("PARAM ------> \n" + printParameter(param));

				if(pstmt!=null) pstmt.clearParameters();
				if(rs!=null) rs.close();
				
				pstmt=conn.prepareStatement(query);
				DBManager.setValues(pstmt, param);			
				pstmt.executeUpdate();
				
				if(pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
				
			}
			
			conn.commit();

			bValue = true;
			Logger.LOG_NOHUP.info("GUBUN TAG : UPDATE  excute!!" );
					
		} catch (SQLException se) {
			conn.rollback();
			se.printStackTrace();
			Logger.LOG_NOHUP.debug(se.getMessage());
		} catch (Exception e) {
			conn.rollback();
			e.printStackTrace();
			Logger.LOG_NOHUP.debug(e.toString()); 				
		} finally {
			conn.setAutoCommit(true);
			//DBManager.release(rs,pstmt,conn);
			if(rs != null) {
				rs.close();
				rs = null;
			}
			
			if(conn != null) {
				conn.close();
				conn = null;
			}
		}

		return bValue;
	}
	


	/**
	 * 
	 * @param v
	 * @return
	 */
	public static String printParameter(Vector v) {
	    StringBuffer sb = new StringBuffer();
	    
	    for (int i  = 0; i < v.size(); i++) {
	        sb.append(" ( ").append( i ).append(" th ) : ");
	        Value pValue = (Value)v.elementAt(i);	        
	        sb.append( pValue.getString() ).append("\n");
	    }
	    
	    return  sb.toString();
	}

	/**
	 * 
	 * @param sQuery
	 * @param sField
	 * @return
	 */
	public static String getRsclob(String sQuery, String sField) {
        
        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        StringBuffer sb = new StringBuffer();
         
        conn=DBManager.getConnection();

        try {
        	Logger.LOG_NOHUP.info("sQuery ------> \n" + sQuery);
                
                pstmt=conn.prepareStatement(sQuery);        
                rs=pstmt.executeQuery();
                int byteRead = 0;
                if (rs.next()) {
                            Reader RdCONTENTS  = rs.getCharacterStream(sField);                                                        
                    
                char[] buffer = new char[1024];
            while((byteRead=RdCONTENTS.read(buffer,0,1024))!=-1) {
                    sb.append(buffer,0,byteRead);
            }
            RdCONTENTS.close();                       
        }
                
                
        } catch (SQLException se) {
                se.printStackTrace();
                Logger.LOG_NOHUP.debug(se.getMessage());
        } catch (Exception e) {
                e.printStackTrace();
                Logger.LOG_NOHUP.debug(e.toString());                                 
        } finally {
                DBManager.release(rs,pstmt,conn);
        }
        //cat.debug(sb.toString());
        return sb.toString();
    }	
	/**
	 * Clob Ÿ���� �ʵ忡 ����,����; '�� Method
	 * 
	 * @param param
	 * @return
	 */
	public static boolean  updatelobQuery (String[] sQuerys, ArrayList aRrayparam, String sContent) {
    
    	Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        Vector v = null;
        boolean bValue = false;
    
        conn=DBManager.getConnection();

        try {
            conn.setAutoCommit(false);
            
                for(int i=0; i<sQuerys.length; i++){
                        String sQuery = sQuerys[i];
                        Vector param = (Vector)aRrayparam.get(i);
                        Logger.LOG_NOHUP.info("QUERY ------> \n" + sQuery);
                        if (i==0) {
                            pstmt=conn.prepareStatement(sQuery);
                            DBManager.setValues(pstmt, param);
                            pstmt.executeUpdate();
                        } else if (i==1) {
                            pstmt=conn.prepareStatement(sQuery);
                            DBManager.setValues(pstmt, param);
                            rs = pstmt.executeQuery();
                            
                            if (rs.next()) {
                                    CLOB clob = ((OracleResultSet)rs).getCLOB(1); 
                                    Writer toClobWriter = clob.getCharacterOutputStream(); // clob�� ������ writer ��
                                    Reader strSrcReader = new CharArrayReader(sContent.toCharArray()); // ���������� Reader ��
                                    char[] buffer = new char[1024]; // read �� ��' ��d
                                    int read = 0;
                                    while ( (read = strSrcReader.read(buffer,0,1024)) != -1)
                                    {
                                            toClobWriter.write(buffer, 0, read); // ���� 
                                    }
                                    strSrcReader.close(); // reader close
                                    toClobWriter.close(); //writer close
                            }
                                                          
                        }
                        
                } // end for
                
            conn.commit();
                bValue = true;
                Logger.LOG_NOHUP.info("GUBUN TAG : UPDATE Clob excute!!" );
                
        } catch (SQLException se) {
                se.printStackTrace();
                Logger.LOG_NOHUP.debug(se.getMessage());
        } catch (Exception e) {
                e.printStackTrace();
                Logger.LOG_NOHUP.debug(e.toString());                                 
        } finally {
                DBManager.release(rs,pstmt,conn);
        }

        return bValue;   
	}
}